package com.zh.BookJDBC;

import java.sql.*;
import java.util.ArrayList;
import java.util.Scanner;

public class Test {

    public static void main(String[] args) throws SQLException, ClassNotFoundException {

        while (true){
            System.out.println("-------hello--------");
            System.out.println("1.查询书籍");
            System.out.println("2.展示书籍");
            System.out.println("3.删除书籍");
            System.out.println("4.增加书籍");
            System.out.println("5.退出应用");

            //借助 Scanner类
            Scanner sc = new Scanner(System.in);
            //利用键盘录入序号  需要提示
            System.out.println("请输入想要执行功能的序号：");
            int choice = sc.nextInt();
            if (choice == 1){
                System.out.println("请输入想要查询的书籍id");
                int bno = sc.nextInt();
               BookJDBC b = findBookByBno(bno);
                if (b == null){
                    System.out.println("查不到书籍");
                }else {
                    System.out.println("查询到编号"+ bno +"的书籍是：《"+b.getName()+ "》");
                }


            }
            if (choice == 2){
                System.out.println("---------2.展示书籍");
                ArrayList books = findBooks();
                if(books.size() == 0){
                    System.out.println("查询不到书籍");
                }else{
                    for (int i = 0; i < books.size(); i++) {
                        BookJDBC b = (BookJDBC) books.get(i);
                        System.out.println(b.getId() +"--"+ b.getName() +"--"+ b.getAuthor() +"--"+ b.getPrice());
                    }
                }


            }
            if (choice == 3){
                System.out.println("---------3.删除书籍");
                //录入想要删除书籍编号
                System.out.println("请输入想要删除的编号");
                int bno = sc.nextInt();
                int n = deleteBookByBno(bno);
                if (n <= 0){
                    System.out.println("删除失败！");
                }else {
                    System.out.println("删除成功");
                }


            }
            if (choice == 4) {
                System.out.println("---------4.增加书籍");
                System.out.println("请输入要增加的书籍的名字、作者和价格：");

                // 读取书籍名字
                System.out.print("书名:  ");
                String name = sc.nextLine();
                System.out.print("作者:  ");
                String author = sc.nextLine();
                System.out.print("价格:  ");
                double price = sc.nextDouble();
                sc.nextLine(); // 消耗掉nextDouble留下的换行符

                // 调用增加书籍的方法，并传入必要的参数（不包括bno）
                int result = addBook(name, author, price);

                // 根据返回值判断增加书籍是否成功
                if (result == 1) {
                    System.out.println("增加书籍成功！");
                } else if (result == 0) {
                    System.out.println("增加书籍失败！（可能没有正确插入数据）");
                } else {
                    System.out.println("增加书籍时发生异常！");
                }
            }

            if (choice == 5){
                System.out.println("---------5.退出应用");
                break;
            }
        }
    }

    //根据编号查询书籍
    public static BookJDBC findBookByBno(int bno) throws ClassNotFoundException, SQLException {
        BookJDBC b =null;
        // 加载数据库驱动类
        Class.forName("com.mysql.cj.jdbc.Driver");

        // 获取数据库连接对象
        Connection conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/zh01?serverTimezone=Hongkong&useUnicode=true&characterEncoding=utf8&useSSL=false",
                "root", "1234");
        //创建会话：
        Statement sta =conn.createStatement();
        //发送SQL：
        ResultSet rs = sta.executeQuery("select * from t_book where id = "+ bno);
        if (rs.next()){//如果结果只有一条
            int id =rs.getInt("id");
            String name =rs.getString("name");
            String author =rs.getString("author");
            double price = rs.getInt("price");

            //将上面数据封装为Book对象:
            b = new BookJDBC();
            b.setId(id);
            b.setName(name);
            b.setAuthor(author);
            b.setPrice(price);


        }

        //关闭资源
        sta.close();
        conn.close();

        return b;

    }

    public static ArrayList findBooks() throws ClassNotFoundException, SQLException {
        ArrayList list = new ArrayList();
        Class.forName("com.mysql.cj.jdbc.Driver");

        // 获取数据库连接对象
        Connection conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/zh01?serverTimezone=Hongkong&useUnicode=true&characterEncoding=utf8&useSSL=false",
                "root", "1234");


        Statement sta = conn.createStatement();
        ResultSet rs = sta.executeQuery("select * from t_book");
        while (rs.next()){
            int id =rs.getInt("id");
            String name =rs.getString("name");
            String author =rs.getString("author");
            double price = rs.getDouble("price");
            BookJDBC b = new BookJDBC();
            b.setId(id);
            b.setName(name);
            b.setAuthor(author);
            b.setPrice(price);
        list.add(b);

        }
        return list;
    }

    public static int deleteBookByBno(int bno) throws ClassNotFoundException, SQLException {

        Class.forName("com.mysql.cj.jdbc.Driver");
        // 获取数据库连接对象
        Connection conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/zh01?serverTimezone=Hongkong&useUnicode=true&characterEncoding=utf8&useSSL=false",
                "root", "1234");
        Statement sta = conn.createStatement();
        int n = sta.executeUpdate("delete from t_book where id="+bno);


        //关闭资源
        sta.close();
        conn.close();

        return n;

    }

    // 假设我们有一个方法用于插入书籍，但不需要书籍编号（bno）
    public static int addBook(String name, String author, double price) throws SQLException {

        Connection connection = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/zh01?serverTimezone=Hongkong&useUnicode=true&characterEncoding=utf8&useSSL=false",
                "root", "1234");
        // 构造SQL语句，不包含bno字段，因为它由数据库自动处理
        String sql = "INSERT INTO t_book (name, author, price) VALUES (?, ?, ?)";

        try (PreparedStatement psta = connection.prepareStatement(sql)) {
            psta.setString(1, name);
            psta.setString(2, author);
            psta.setDouble(3, price);

            // 执行更新，并返回受影响的行数
            int rowsAffected = psta.executeUpdate();

            // 通常，如果插入成功，受影响的行数为1
            if (rowsAffected > 0) {
                // 可以在这里查询新插入书籍的编号（如果需要的话）
                // 但通常这不是必要的，除非您立即需要这个编号进行后续操作
                return 1; // 表示插入成功
            } else {
                return 0; // 表示插入失败
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return -1; // 表示发生异常，插入失败
        }
    }

}




